using System;
using System.IO;
using System.Data;
using System.Data.OracleClient;

namespace OracleTest
{
	public class DBClass
	{
		private OracleConnection dbcon = null;
	
		public void Connect ()
		{
			string connectionString =
				"Data Source=//10.10.13.1/XE;" +
				"User ID=radu;" +
				"Password=panamea;";
				
			dbcon = new OracleConnection (connectionString);
			
			try {
				dbcon.Open ();
			} catch (OracleException e) {
				Console.WriteLine ("OracleException");
				Console.WriteLine("Cod: " + e.Code + "\n" +
					"Message: " + e.Message);
			}
		}
		
		public void Close ()
		{
			if (dbcon != null) { 
				dbcon.Close ();
				dbcon = null;
			}
		}
		
		public void SimpleQuery ()
		{
			string sql = "SELECT deptno, sname FROM dept";

			OracleCommand dbcmd = dbcon.CreateCommand ();
			dbcmd.CommandText = sql;
			
			OracleDataReader reader;
			try {
				reader = dbcmd.ExecuteReader ();
				while (reader.Read ()) {
					string deptno = reader["deptno"].ToString ();
					string sname = reader["sname"].ToString ();
					Console.WriteLine ("Deptno: {0}  Sname: {1}", deptno, sname);
				}

				// clean up
				reader.Close ();
				reader = null;
				
				dbcmd.CommandText = sql;
				dbcmd.ExecuteNonQuery ();
			} catch (OracleException ex) {
				Console.WriteLine ("A avut loc o exceptie Oracle!");
			}
			dbcmd.Dispose ();
			dbcmd = null;
		}
		
		public void ExecuteScalar ()
		{
			string sql = "SELECT COUNT(*) FROM users WHERE user_name='radu'";

			OracleCommand dbcmd = dbcon.CreateCommand ();
			dbcmd.CommandText = sql;
			
			object ob;
			ob = dbcmd.ExecuteScalar();
			
			int no = Int32.Parse (ob.ToString ());
			Console.WriteLine ("ExecuteScalar: no=" + no);
			
			dbcmd.Dispose ();
		}
		
		public void ParametersTest()
		{
			/* OBS: parametri care indica stringuri nu trebuiesc pusi intre ghilimele */
			OracleCommand dbcmd = dbcon.CreateCommand ();
			dbcmd.CommandText = "UPDATE people SET person_name=:P1, person_birthday=:P2, " +
				"person_country_id=:P3 WHERE person_id=:P4";
			dbcmd.Parameters.Add(":P1", OracleType.VarChar, 20).Value = "moshi ppp"; 
			dbcmd.Parameters.Add(":P2", OracleType.VarChar, 20).Value = "moshi desc";
			dbcmd.Parameters.Add(":P3", OracleType.Number).Value = 1; 
			dbcmd.Parameters.Add(":P4", OracleType.Number).Value = 1;
			
			dbcmd.ExecuteNonQuery();

			dbcmd.Dispose ();
		}

		public void InsertImage()
		{
			// citesc imaginea
			string imgPath = @"/mnt/hdd1/work/school/ProiectPSBD/OracleTest/movie.jpg";
			FileStream imgStream = new FileStream (imgPath, FileMode.Open, FileAccess.Read);
			byte[] imgData = new byte[imgStream.Length];
			imgStream.Read (imgData, 0, System.Convert.ToInt32(imgStream.Length));
			imgStream.Close ();

			// execut query
			string sqlQuery = "insert into images(id, name, photo) values(1, 'prima imagine', :BlobParameter)";
			
			OracleParameter imgDataParam = new OracleParameter();
			imgDataParam.OracleType = OracleType.Blob;
			imgDataParam.ParameterName = "BlobParameter";
			imgDataParam.Size = imgData.Length;
			imgDataParam.Value = imgData;
			
			OracleCommand cmnd = new OracleCommand (sqlQuery, dbcon);
			cmnd.Parameters.Add (imgDataParam);
			cmnd.ExecuteNonQuery();
			cmnd.Dispose ();
		}
		
		public void RetriveImage ()
		{
			OracleDataAdapter adap = new OracleDataAdapter();
			adap.SelectCommand = new OracleCommand("SELECT * FROM images", dbcon);
			DataSet dataSet = new DataSet("dataSet");
			adap.Fill(dataSet);
			
			DataTable dataTable;
			dataTable = dataSet.Tables[0];
			
			string imgPath = @"/mnt/hdd1/work/tests/c_sharp/OracleTest/movie2.jpg";
			FileStream fs = new FileStream(imgPath, FileMode.Create);
			
			foreach(DataRow dataRow in dataTable.Rows) {
				byte[] imgData = (byte[]) dataRow[2];
				fs.Write (imgData, 0, imgData.Length);
				fs.Close ();
				break;
			}
		}
		
		// metoda alternativa de citire a unei imagini
		public void RetriveImage2 ()
		{
			string imgPath = @"/mnt/hdd1/work/tests/c_sharp/OracleTest/movie2.jpg";
			FileStream fs = new FileStream(imgPath, FileMode.Create);
			
			/* OBS: parametri care indica stringuri nu trebuiesc pusi intre ghilimele */
			OracleCommand dbcmd = dbcon.CreateCommand ();
			// tranzactia e necesara pt. lucrul cu LOB
			dbcmd.Transaction = dbcon.BeginTransaction ();
			dbcmd.CommandText = "SELECT * FROM images WHERE id=1";
			
			try {
				OracleDataReader reader = dbcmd.ExecuteReader ();
				if (reader.Read ()) {
					OracleLob lob = reader.GetOracleLob (2);
					byte[] buffer = new byte[100];
					int no = 0;
					while ((no = lob.Read (buffer, 0, 100)) > 0) {
						fs.Write (buffer, 0, buffer.Length);
					}
					fs.Close ();
				} else {
					Console.WriteLine ("nu am citit nimic!");
				}
			} catch (OracleException ex) {
				Console.WriteLine (ex.Message);
				return;
			}
			
			dbcmd.Dispose ();
			
		}
		
		public void StoredProcedures()
		{
			OracleCommand dbcmd = dbcon.CreateCommand();
			dbcmd.CommandText = "DECLARE \n" + 
				"BEGIN \n" + 
				"insert_image (:P1, :P2); \n" +
				"END;"; 
			dbcmd.Parameters.Add("P1", OracleType.Int32).Value = "2";
			dbcmd.Parameters.Add("P2", OracleType.VarChar, 100).Value = "test desc";
			
			dbcmd.ExecuteNonQuery ();
			
			dbcmd.Dispose ();
			dbcmd = null;
		}

		public void StoredProcedures2()
		{
			OracleCommand dbcmd = dbcon.CreateCommand();
			dbcmd.CommandText = "insert_image";
			dbcmd.CommandType = CommandType.StoredProcedure;
			dbcmd.Parameters.Add("id_arg", OracleType.Int32).Value = "3";
			dbcmd.Parameters.Add("name_arg", OracleType.VarChar, 100).Value = "test desc";
			
			dbcmd.ExecuteNonQuery ();
			
			dbcmd.Dispose ();
			dbcmd = null;
		}
		
		/* OBS: nu pot executa functii stocate, primesc eroarea:
		 * System.NullReferenceException: Object reference not set to an instance of an object
		 */
		public void StoredFunctions()
		{
			OracleCommand dbcmd = dbcon.CreateCommand();
			dbcmd.CommandText = "func_test";
			dbcmd.CommandType = CommandType.StoredProcedure;
			
			OracleParameter retParam = new OracleParameter ();
			retParam.OracleType = OracleType.Int32;
			retParam.Direction = ParameterDirection.ReturnValue;
			dbcmd.Parameters.Add (retParam);
			
			dbcmd.ExecuteScalar ();

			dbcmd.Dispose ();
			dbcmd = null;
		}
		
		/* testeaza atat lucrul cu pachetele cat si cu RefCursor */
		public void RefCursor()
		{
			/* OBS: ca sa am acces la restul functiilor din pachet, mai intai 
			 * trebuie sa ma loghez folosind procedura "test_pkng.login"
			 */
			OracleCommand dbcmd = dbcon.CreateCommand();
			dbcmd.CommandText = "test_pkg.login";
			dbcmd.CommandType = CommandType.StoredProcedure;
			
			dbcmd.Parameters.Add ("p_user", OracleType.VarChar, 60).Value = "ghita";
			dbcmd.Parameters.Add ("p_password", OracleType.VarChar, 60).Value = "portita";
			
			OracleParameter resParam = new OracleParameter ("p_res", OracleType.Number);
			resParam.Direction = ParameterDirection.Output;
			dbcmd.Parameters.Add (resParam);
			
			dbcmd.ExecuteNonQuery ();
			
			Console.WriteLine ( resParam.Value.GetType ().ToString ());
			
			decimal res = (decimal)  resParam.Value;
			if (res == 0) {
				Console.WriteLine ("Nu m-am putut autentifica");
				return;
			}
			
			// acum pot sa lucrez cu restul procedurilor din pachet
			dbcmd = dbcon.CreateCommand();
			dbcmd.CommandText = "test_pkg.select_test";
			dbcmd.CommandType = CommandType.StoredProcedure;
			
			OracleParameter cursorParam = new OracleParameter ("p_cursor", OracleType.Cursor);
			cursorParam.Direction = ParameterDirection.Output;
			dbcmd.Parameters.Add (cursorParam);
			
			resParam = new OracleParameter ("p_res", OracleType.Int32);
			resParam.Direction = ParameterDirection.Output;
			dbcmd.Parameters.Add (resParam);
			
			dbcmd.ExecuteNonQuery ();
			
			try {
				OracleDataReader reader = (OracleDataReader) cursorParam.Value;
				while (reader.Read ()) {
					string user = reader.GetString (0);
					string pass = reader.GetString (1);
					
					Console.WriteLine (user + " " + pass);
				}
			} catch (OracleException ex) {
				Console.WriteLine (ex.Message);
				return;
			}
			
			res = (decimal) resParam.Value;
			if (res == 0) {
				Console.WriteLine ("Nu m-am putut autentifica");
				return;
			} 
			
			dbcmd.Dispose ();
			dbcmd = null;
		}
	}
}
